IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPersonByUserId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetPersonByUserId]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************************************************************************************************    
* Name:  GetPersonByUserId
* Purpose: Stored procedure for retrieving a person    
  Note:Two fields(Outside member & COIRole) added to the existing resultset  
* PARAMETERS      
* Name   Description    
* ------------------------- -----------------------------    
* @UserId  FwkDomainUser Id    
*              
************************************************************************************************/    
    
CREATE procedure [dbo].[GetPersonByUserId]
(    
 @userId int    
)    
as    
begin    

   set nocount on

   declare @email varchar(100)
   execute @email = dbo.[GetServerEMail]

   if @email is NULL
      select     
         ap.Id,
         ap.FirstName,    
         ap.LastName,    
         (ap.LastName + ', ' + ap.FirstName) as 'FullName',    
         fdu.Username,    
         fdo.FullyQualifiedName as 'OrganizationName',    
         fdo.[Id] as 'OrganizationId',    
         ap.AdmPersonTypeId,    
         map.RecomAbstractId,    
         fdu.[Id] as 'UserId',  
         ap.COIRole,  
         ap.OutsideMember,  
         aea.EmailAddress,		 
		 atn.AreaCode,
		 atn.Number as PhoneNumber,
		 atnFax.AreaCode as FaxArea,	       
		 atnFax.Number as FaxNumber	      
      from AdmPerson ap    
         left join FwkDomainUser fdu on ap.FwkDomainUserId = fdu.[Id]    
         left join AdmInternalPerson aip on ap.[Id] = aip.AdmPersonId    
         left join FwkDomainOrganization fdo on aip.PrimaryOrganizationId = fdo.[Id]    
         left join AdmPerson_recomAbstract_Map map on map.AdmPersonId = ap.Id    
         LEFT JOIN AdmEmailAddress aea ON ap.Id = aea.AdmPersonId AND aea.AdmEmailAddressTypeId = 1
		 LEFT Join AdmTelephoneNumber atn ON ap.Id = atn.admPersonId AND atn.AdmTelephoneTypeId = 1  
		 Left Join AdmTelephoneNumber atnFax On ap.Id = atnFax.admPersonId AND atnFax.AdmTelephoneTypeId = 3      
      where fdu.[Id] = @userId
   else 
      select     
         ap.Id,
         ap.FirstName,    
         ap.LastName,    
         (ap.LastName + ', ' + ap.FirstName) as 'FullName',    
         fdu.Username,    
         fdo.FullyQualifiedName as 'OrganizationName',    
         fdo.[Id] as 'OrganizationId',    
         ap.AdmPersonTypeId,    
         map.RecomAbstractId,    
         fdu.[Id] as 'UserId',  
         ap.COIRole,  
         ap.OutsideMember,  
         @email as 'EmailAddress',		 
		 atn.AreaCode,
		 atn.Number as PhoneNumber,
		 atnFax.AreaCode as FaxArea,	       
		 atnFax.Number as FaxNumber	     
      from AdmPerson ap    
         left join FwkDomainUser fdu on ap.FwkDomainUserId = fdu.[Id]    
         left join AdmInternalPerson aip on ap.[Id] = aip.AdmPersonId    
         left join FwkDomainOrganization fdo on aip.PrimaryOrganizationId = fdo.[Id]    
         left join AdmPerson_recomAbstract_Map map on map.AdmPersonId = ap.Id
		 LEFT Join AdmTelephoneNumber atn ON ap.Id = atn.admPersonId AND atn.AdmTelephoneTypeId = 1  
		 Left Join AdmTelephoneNumber atnFax On ap.Id = atnFax.admPersonId AND atnFax.AdmTelephoneTypeId = 3     
      where fdu.[Id] = @userId
end    



